<html>
<head></head>
<body>
<?php

// Settings
$SQL_Server = "localhost";
$SQL_Username = "root";
$SQL_Password = "";
$SQL_Database = "ccl";
$CCL_Locations_Table_CSV = getcwd() . "/LOCATION.TXT";
$CCL_Volume_Table_CSV = getcwd() . "/VOLUME.TXT";
$CCL_Subject_Table_CSV = getcwd() . "/SUBJECT.TXT";
$CCL_Author_Table_CSV = getcwd() . "/AUTHOR.TXT";
$CCL_Author_Volume_Table_CSV = getcwd() . "/AUTH_VOL.TXT";
$CCL_Subject_Volume_Table_CSV = getcwd() . "/SUBJ_VOL.TXT";
$CCL_Subscriber_Table_CSV = getcwd() . "/SUBSCRBR.TXT";
$CCL_Subscriber_Paid_Table_CSV = getcwd() . "/SUBSPAID.TXT";
$CCL_Loan_Table_CSV = getcwd() . "/LOAN.TXT";
$CCL_Loan_History_Table_CSV = getcwd() . "/ARCHIV_L.TXT";

// Set unlimited time limit
set_time_limit(0);

// Connect to SQL Server
echo 'Connecting to server: ' . $SQL_Server . '<br/>';
$connection = mysqli_connect($SQL_Server, $SQL_Username, $SQL_Password, $SQL_Database);
if (!$connection)
{
	die('Could not connect: ' . mysqli_error($connection));
}

// Import Locations Table
echo '<strong>Importing Locations Table from file: ' . $CCL_Locations_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Locations_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Locations_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 3)
	{
		$SQL_Query = 'INSERT INTO tbl_location VALUES ("' . $data[0] . '","'	// Location ID
														. $data[1] . '")';		// Location Name
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Volume Table
echo '<strong>Importing Volumes Table from file: ' . $CCL_Volume_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Volume_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Volume_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 9)
	{
		// If no location is set, set it to unknown
		if (!$data[6]) $data[6] = 'U';
		
		$data[2] = addslashes($data[2]);
		$SQL_Query = 'INSERT INTO tbl_volume VALUES		 ("' . $data[0] . '"' .	// Access ID
														',"' . $data[1] . '"' .	// Call Number
														',"' . $data[2] . '"' .	// Title
														',"' . $data[3] . '"' .	// Publisher Name
														',"' . $data[4] . '"' .	// Publisher Date
														',"' . $data[5] . '"' .	// ISBN
														',"' . $data[6] . '"' .	// Location ID
														',"' . $data[8] . '")';	// Catalogue Date
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Subject Table
echo '<strong>Importing Subject Table from file: ' . $CCL_Subject_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Subject_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Subject_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 3)
	{
		$data[1] = addslashes($data[1]);
		$SQL_Query = 'INSERT INTO tbl_subject VALUES	 ("' . $data[0] . '"' .	// Subject ID
														',"' . $data[1] . '")';	// Subject Name
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Author Table
echo '<strong>Importing Author Table from file: ' . $CCL_Author_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Author_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Author_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 3)
	{
		$data[1] = addslashes($data[1]);
		$SQL_Query = 'INSERT INTO tbl_author VALUES		 ("' . $data[0] . '"' .	// Author ID
														',"' . $data[1] . '")';	// Author Name
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Author-Volume Table
echo '<strong>Importing Author-Volume Table from file: ' . $CCL_Author_Volume_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Author_Volume_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Author_Volume_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 2)
	{
		$SQL_Query = 'INSERT INTO tbl_author_volume VALUES		 ("' . $data[0] . '"' .	// Access ID
																	',"' . $data[1] . '")';	// Author ID
		if (!mysqli_query($connection, $SQL_Query))
		{
			//echo 'Error: ' . mysqli_error($connection) . '<br/>';
			//foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Subject-Volume Table
echo '<strong>Importing Subject-Volume Table from file: ' . $CCL_Subject_Volume_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Subject_Volume_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Subject_Volume_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 2)
	{
		$SQL_Query = 'INSERT INTO tbl_subject_volume VALUES			 ("' . $data[0] . '"' .	// Access ID
																	',"' . $data[1] . '")';	// Subject ID
		if (!mysqli_query($connection, $SQL_Query))
		{
			//echo 'Error: ' . mysqli_error($connection) . '<br/>';
			//foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Subscriber Table
echo '<strong>Importing Subscriber Table from file: ' . $CCL_Subscriber_Table_CSV . '</strong><br/>';
$subscriber_id_conversion = array();
$subscriber_id_conversion[''] = 0; 
$sub_id_count = 0;
$file_handle = fopen($CCL_Subscriber_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Subscriber_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
		
	$sub_id_count++;
	$subscriber_id_conversion[$data[0]] = $sub_id_count;
    
	if (count($data) == 14)
	{
		$data[1] = addslashes($data[1]);
		$SQL_Query = 'INSERT INTO tbl_subscriber VALUES ("' . $subscriber_id_conversion[$data[0]] . '"' .	// Subscriber ID
														',"' . $data[1] . '"' .	// Last Name
														',"' . $data[2] . '"' .	// First Name
														',"' . $data[3] . '"' .	// Initials
														',"' . $data[4] . '"' .	// Title
														',"' . $data[5] . '"' .	// Address1
														',"' . $data[6] . '"' .	// Address2
														',"' . $data[7] . '"' .	// Suburb
														',"' . $data[8] . '"' .	// Post Code
														',"' . $data[9] . '"' .	// Phone
														',"' . $data[10] . '"' .	// Mobile Phone
														',"' . $data[11] . '"' .	// Work Phone
														',"' . $data[12] . '"' .	// Subscriber Status
														',"' . '' . '")';	// Email
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Subscriber Paid Table
echo '<strong>Importing Subscriber Paid Table from file: ' . $CCL_Subscriber_Paid_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Subscriber_Paid_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Subscriber_Paid_Table_CSV_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 5)
	{
		$data[1] = addslashes($data[1]);
		$SQL_Query = 'INSERT INTO tbl_subscriber_paid VALUES ("' . $subscriber_id_conversion[$data[0]] . '"' .	// Subscriber ID
															',"' . $data[1] . '"' .	// Date Paid
															',"' . $data[2] . '"' .	// Receipt Number
															',"' . $data[3] . '"' .	// Amount
															',"' . $data[4] . '")';	// Expiry Date
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}

	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Loan Table
echo '<strong>Importing Loan Table from file: ' . $CCL_Loan_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Loan_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Loan_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 7)
	{
		$SQL_Query = 'INSERT INTO tbl_loan VALUES		 ("' . $data[0] . '"' .	// Access ID
														',"' . $subscriber_id_conversion[$data[1]] . '"' .	// Subscriber ID
														',"' . $data[2] . '"' .	// Loan Date
														',"' . $data[3] . '"' .	// Extension Date
														',"' . $data[4] . '"' .	// Due Date
														',"' . $data[5] . '"' .	// Return Date
														',"' . $subscriber_id_conversion[$data[6]] . '")';	// Reserve Subscriber
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Import Loan History Table
echo '<strong>Importing Loan History Table from file: ' . $CCL_Loan_History_Table_CSV . '</strong><br/>';
$file_handle = fopen($CCL_Loan_History_Table_CSV, "r");
if (!$file_handle)
	die('Could not open file ' . $CCL_Loan_History_Table_CSV);
while (($data = fgetcsv($file_handle)) !== FALSE)
{
	if (count($data) == 7)
	{
		$SQL_Query = 'INSERT INTO tbl_loan_history VALUES		 ("' . $data[0] . '"' .	// Access ID
																',"' . $subscriber_id_conversion[$data[1]] . '"' .	// Subscriber ID
																',"' . $data[2] . '"' .	// Loan Date
																',"' . $data[3] . '"' .	// Extension Date
																',"' . $data[4] . '"' .	// Due Date
																',"' . $data[5] . '"' .	// Return Date
																',"' . $subscriber_id_conversion[$data[6]] . '")';	// Reserve Subscriber
		if (!mysqli_query($connection, $SQL_Query))
		{
			echo 'Error: ' . mysqli_error($connection) . '<br/>';
			foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
		}
	}
	
	else
	{
		echo 'Error: Incorrect column count: ' . $data . '<br/>';
		foreach ($data as $field) echo $field . ', '; echo '<br/><br/>';
	}
}
fclose($file_handle);

// Close SQL Connection
mysqli_close($connection);

echo '<h1>Subscriber ID Conversion</h1><table>';
foreach ($subscriber_id_conversion as $key=>$value)
	echo '<tr><td>' . $key . '</td><td>' . $value . '</td></tr>';
echo '</table>';
?>
</body>
</html>